package com.dsz.duty.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.dsz.duty.model.DutyRecord;

/**
 * @author crjun
 */
@Repository
public class DutyRecordDao extends JdbcDaoSupport implements IDutyRecordDao {
	private static DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	/*  key=value&key='value'&key like %value%&key like '%value%'*/
    private String getSql(int page, int rows, String filter) {
        String sql = null;
        if (null == filter || filter.trim().equals("")) {
            sql = "select top " + rows + " ID, DUTYMAN, DUTYTIME, ENTRYTIME, PRODUCTIONSITUATION, SECURITY, SECURITYPRODUCTION, POSTRESPONSIBILITY, LEFTPROBLEM, DUTYMANFULLNAME from DUTYRECORD where ID not in(select top " + (page - 1) * rows + " ID from DUTYRECORD order by DUTYTIME desc) order by DUTYTIME desc";

        }  else
            sql = "select top " + rows + " ID, DUTYMAN, DUTYTIME, ENTRYTIME, PRODUCTIONSITUATION, SECURITY, SECURITYPRODUCTION, POSTRESPONSIBILITY, LEFTPROBLEM, DUTYMANFULLNAME from DUTYRECORD where ID not in(select top " + (page - 1) * rows + " ID from DUTYRECORD where " + filter + "  order by DUTYTIME desc) and " + filter + " order by DUTYTIME desc";
        return sql;
    }
    
	@Override
	public List<DutyRecord> getPageDutyRecords(String filter, int page, int rows) {
		return (List<DutyRecord>)getJdbcTemplate().query(getSql(page, rows, filter), new Object[] {}, new DutyRecordRowMapper());
	}

	private static final String ADD_DUTYRECORD = "INSERT INTO DUTYRECORD(DUTYMAN, DUTYMANFULLNAME, DUTYTIME, ENTRYTIME, PRODUCTIONSITUATION, SECURITY, SECURITYPRODUCTION, POSTRESPONSIBILITY, LEFTPROBLEM) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
	@Override
	public void addDutyRecord(DutyRecord dutyRecord) {
		getJdbcTemplate().update(
				ADD_DUTYRECORD,
				new Object[] { 
						dutyRecord.getDutyMan(),
						dutyRecord.getDutyManFullName(),
						dutyRecord.getDutyTime(),
						dutyRecord.getEntryTime(),
//						df.format(new Date()),
						dutyRecord.getProductionSituation() == null || dutyRecord.getProductionSituation().trim().equals("") ? "无" : dutyRecord.getProductionSituation(),
						dutyRecord.getSecurity() == null || dutyRecord.getSecurity().trim().equals("") ? "无" : dutyRecord.getSecurity(),
						dutyRecord.getSecurityProduction() == null || dutyRecord.getSecurityProduction().trim().equals("") ? "无" : dutyRecord.getSecurityProduction(),
						dutyRecord.getPostResponsibility() == null || dutyRecord.getPostResponsibility().trim().equals("") ? "无" : dutyRecord.getPostResponsibility(),
						dutyRecord.getLeftProlem() == null || dutyRecord.getLeftProlem().trim().equals("") ? "无" : dutyRecord.getLeftProlem()
								});
	}

	private static final String REMOVE_DUTYRECORD = "DELETE FROM DUTYRECORD WHERE ID = ?";
	@Override
	public void removeDutyRecord(int id) {
		getJdbcTemplate().update(
				REMOVE_DUTYRECORD,
				new Object[] { id });
	}

	private static final String UPDATE_DUTYRECORD = "UPDATE DUTYRECORD SET DUTYMAN = ?, DUTYMANFULLNAME = ?, DUTYTIME = ?, ENTRYTIME = ?, PRODUCTIONSITUATION = ?, SECURITY = ?, SECURITYPRODUCTION = ?, POSTRESPONSIBILITY = ?, LEFTPROBLEM = ? WHERE ID = ?";
	@Override
	public void updateDutyRecord(DutyRecord dutyRecord) {
		getJdbcTemplate().update(
				UPDATE_DUTYRECORD,
				new Object[] { 
						dutyRecord.getDutyMan(),
						dutyRecord.getDutyManFullName(),
						dutyRecord.getDutyTime(),
						dutyRecord.getEntryTime(),
						dutyRecord.getProductionSituation() == null || dutyRecord.getProductionSituation().trim().equals("") ? "无" : dutyRecord.getProductionSituation(),
						dutyRecord.getSecurity() == null || dutyRecord.getSecurity().trim().equals("") ? "无" : dutyRecord.getSecurity(),
						dutyRecord.getSecurityProduction() == null || dutyRecord.getSecurityProduction().trim().equals("") ? "无" : dutyRecord.getSecurityProduction(),
						dutyRecord.getPostResponsibility() == null || dutyRecord.getPostResponsibility().trim().equals("") ? "无" : dutyRecord.getPostResponsibility(),
						dutyRecord.getLeftProlem() == null || dutyRecord.getLeftProlem().trim().equals("") ? "无" : dutyRecord.getLeftProlem(),
						dutyRecord.getId()
						});
	}
	
	private static final String GET_DUTY_RECORDS_COUNT = "SELECT COUNT(*) FROM DUTYRECORD";

	@Override
	public int getDutyRecordsCount(String filter) {
        if (null == filter || filter.trim().equals("")) {
    		return getJdbcTemplate().queryForInt(GET_DUTY_RECORDS_COUNT);
        }
		return getJdbcTemplate().queryForInt(GET_DUTY_RECORDS_COUNT + " WHERE " + filter);
	}

	@Resource(name = "dataSource")
	public void setSuperDataSource(DataSource dataSource) {
		super.setDataSource(dataSource);
	}

	public static class DutyRecordRowMapper implements RowMapper<DutyRecord> {

		@Override
		public DutyRecord mapRow(ResultSet rs, int i) throws SQLException {
			DutyRecord dutyRecord = new DutyRecord();
			dutyRecord.setId(rs.getInt("ID"));
			dutyRecord.setDutyMan(rs.getString("DUTYMAN"));
			String dt = rs.getString("DUTYTIME");
			dutyRecord.setDutyTime(dt);
			String et = rs.getString("ENTRYTIME");
			dutyRecord.setEntryTime(et);
			dutyRecord.setProductionSituation(rs.getString("PRODUCTIONSITUATION"));
			dutyRecord.setSecurity(rs.getString("SECURITY"));
			dutyRecord.setSecurityProduction(rs.getString("SECURITYPRODUCTION"));
			dutyRecord.setPostResponsibility(rs.getString("POSTRESPONSIBILITY"));
			dutyRecord.setLeftProlem(rs.getString("LEFTPROBLEM"));
			dutyRecord.setDutyManFullName(rs.getString("DUTYMANFULLNAME"));
			return dutyRecord;
		}

	}
}
